package com.syq.utils;

import com.alibaba.druid.pool.DruidDataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.ResourceBundle;

public class DBUtils {
    private Connection connection = null;
    private PreparedStatement preparedStatement =null;
    private ResultSet resultSet =null ;
    private int count;//受影响的行数

    private static String userName;
    private static String userPwd;
    private static String url;
    private static String driverName;

    //德鲁伊
    private static DruidDataSource dataSource = new DruidDataSource();

    //加载驱动
    static {
        ResourceBundle bundle = ResourceBundle.getBundle("db");
        driverName = bundle.getString("driverName");
        userName = bundle.getString("userName");
        userPwd = bundle.getString("userPwd");
        url = bundle.getString("url");

        dataSource.setUsername(userName);
        dataSource.setPassword(userPwd);
        dataSource.setUrl(url);
        dataSource.setDriverClassName(driverName);
    }
    //获取连接
    protected Connection getConnection() {
        try {
            connection = dataSource.getConnection();
        } catch (SQLException e) {
            //e.printStackTrace();
        }
        return connection;
    }
    //获取预状态通道
    protected PreparedStatement getPreparedStatement(String sql) {
        try {
            preparedStatement = getConnection().prepareStatement(sql);
        } catch (SQLException e) {
            //e.printStackTrace();
        }
        return preparedStatement;
    }
    //绑定参数
    protected void param(List list) {
        if(list!=null&&list.size()>0) {
            for (int i=0;i<list.size();i++) {
                try {
                    preparedStatement.setObject(i+1,list.get(i));
                } catch (SQLException e) {
                    //e.printStackTrace();
                }
            }
        }
    }
    //执行操作 增删改
    protected int update(String sql, List list) {
        getPreparedStatement(sql);
        param(list);
        try {
            count = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            //e.printStackTrace();
        }

        return count;
    }

    protected ResultSet query(String sql,List list) {
        getPreparedStatement(sql);
        param(list);
        try {
            resultSet = preparedStatement.executeQuery();
        } catch (SQLException e) {
            //e.printStackTrace();
        }
        return resultSet;
    }

    //关闭资源
    protected void closeAll() {
        try {
            if(resultSet!=null) {
                resultSet.close();
            }
            if(preparedStatement!=null) {
                preparedStatement.close();
            }
            if(connection!=null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
